{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 合并本期已入库未结算\n",
    "\n",
    "这个涉及到一个从 物料申请表 到 t+编码 和 仓库 的 数据表的 过程。这个其实也简单应该，就是A表 先套装拆件压缩,再编码匹配，再到数据源模版，这个数据源模版就可以用了。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 导入物料申请管理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb_成本价=xw.Book(r'K:\\BaiduSyncdisk\\王振洋资料\\1.商贸公司资料\\2月商贸公司资料\\11.本月暂估入库\\成本价.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "成本价=wb_成本价.sheets(\"成本价原始\").range('a1').expand().options(pd.DataFrame).value\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "成本价=(\n",
    "    成本价.reset_index()\n",
    "    .loc[:,['商品id','商品名称','申请城市','审核状态','收货总件数']]\n",
    "    .query(\"审核状态 != '已结算' and 审核状态!='' \" )\n",
    "    .drop(0) #删除第一行\n",
    "    .assign(城市2=lambda x:x['申请城市'].str.replace('市',\"\"))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "成本价=成本价.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 在物料申请管理里匹配编码等，转换为t+存货和仓库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 打开t+基础数据表并写入相关数据，为匹配做准备\n",
    "wb基础数据=xw.Book(r't+基础数据.xlsx')\n",
    "\n",
    "存货档案=wb基础数据.sheets('存货档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "存货档案=存货档案.loc[:,['存货编码','存货名称','计量单位','收入科目编码','收入科目名称']]\n",
    "\n",
    "# 匹配 存货编码、仓库编码、部门编码等\n",
    "\n",
    "合并=pd.merge(成本价,存货档案,left_on='商品名称',right_on='存货名称',how='left')\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>商品名称</th>\n",
       "      <th>成本价</th>\n",
       "      <th>存货编码</th>\n",
       "      <th>存货名称</th>\n",
       "      <th>计量单位</th>\n",
       "      <th>收入科目编码</th>\n",
       "      <th>收入科目名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>夏季套装</td>\n",
       "      <td>108.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>专业套装 （新）</td>\n",
       "      <td>235.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>签约马甲套装</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>基础套装（2）</td>\n",
       "      <td>26.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>夏季马甲</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>电子秤</td>\n",
       "      <td>19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>防飞溅护目镜</td>\n",
       "      <td>8.0</td>\n",
       "      <td>010254</td>\n",
       "      <td>防飞溅护目镜</td>\n",
       "      <td>个</td>\n",
       "      <td>60010254</td>\n",
       "      <td>防飞溅护目镜收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>防水手机袋（活动）</td>\n",
       "      <td>5.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>头盔（竞对置换）</td>\n",
       "      <td>35.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>208</th>\n",
       "      <td>胸牌</td>\n",
       "      <td>1.0</td>\n",
       "      <td>010106</td>\n",
       "      <td>胸牌</td>\n",
       "      <td>个</td>\n",
       "      <td>60010106</td>\n",
       "      <td>胸牌收入</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>209 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          商品名称    成本价    存货编码    存货名称 计量单位    收入科目编码    收入科目名称\n",
       "0         夏季套装  108.0     NaN     NaN  NaN       NaN       NaN\n",
       "1     专业套装 （新）  235.0     NaN     NaN  NaN       NaN       NaN\n",
       "2       签约马甲套装  105.0     NaN     NaN  NaN       NaN       NaN\n",
       "3      基础套装（2）   26.0     NaN     NaN  NaN       NaN       NaN\n",
       "4         夏季马甲   25.0     NaN     NaN  NaN       NaN       NaN\n",
       "..         ...    ...     ...     ...  ...       ...       ...\n",
       "204        电子秤   19.0     NaN     NaN  NaN       NaN       NaN\n",
       "205     防飞溅护目镜    8.0  010254  防飞溅护目镜    个  60010254  防飞溅护目镜收入\n",
       "206  防水手机袋（活动）    5.5     NaN     NaN  NaN       NaN       NaN\n",
       "207   头盔（竞对置换）   35.0     NaN     NaN  NaN       NaN       NaN\n",
       "208         胸牌    1.0  010106      胸牌    个  60010106      胸牌收入\n",
       "\n",
       "[209 rows x 7 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb_成本价.sheets('编码匹配中间表').range('a1').value=合并"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 合并当期已入库未结算"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
